import pyodbc
from flask import Flask

app = Flask(__name__) # 实例app


@app.route("/checkin")
def checkin():
    # 连接到 SQL Server 数据库，指定端口
    connection_string = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=172.16.106.40,1433;DATABASE=zkprd;UID=sa;PWD=INIT@1234zk"
    conn = pyodbc.connect(connection_string)
    # 创建一个游标对象
    cursor = conn.cursor()
    # 执行 SQL 查询
    sql_query = "SELECT TOP 10 FORMAT(checktime, 'yyyy-MM-dd HH:mm:ss') AS FormattedDate,checktype from checkinout a INNER JOIN userinfo b on a.userid=b.userid and badgenumber='31000072' ORDER BY checktime desc"
    cursor.execute(sql_query)
    # 获取查询结果
    results = cursor.fetchall()
    # 打印查询结果
    ret = []
    for row in results:
        rowStr=", ".join(map(str, row))
        ret.append(rowStr)
        print(rowStr)
    # 关闭连接
    conn.close()
    return  ret